﻿use jsj_new

--1.对教师表teacher中的教师号tno创建聚簇索引，并按降序排序
go
create clustered index teacher_tno on teacher(tno desc)

--2..对学生选课表sc，先按课号cno升序排序，再按成绩score降序排序
go
select * from sc order by cno,score desc

--3.对学生表stusent中的学号sno创建唯一索引，并按升序排序
create unique index student_sno on student(sno asc)

--4.创建成绩视图 SCORE_View，包含学号sno、姓名sname、课程名cname、成绩score
go
create view SCORE_View
as select sc.sno,sname,cname,score from sc
join student on student.sno=sc.sno
join course on course.cno=sc.cno

select * from SCORE_View
--5.创建一个计算机系学生名单视图S_View，包含学号sno、姓名sname、性别sex
go
create view S_View
as select a.sno,sname,score,b.sex from sc a
join student b on b.sno=a.sno

select * from S_View
--6.通过上面的视图SCORE_View，修改学号为991102、课号为01001的成绩记录
update SCORE_View set score = 90 where sno='991102' and cname =(select cname from course where cno='01001')

select * from sc
--7.通过上面的视图S_View，把计算机系的“王蕾”的性别修改为“男”
update S_View set sex = '男' where sname='王蕾'

select * from student
--8.创建一个计算机系学生的成绩单视图SCORE_View_CDEPT，包含学号sno、姓名sname、	课程名cname、成绩score
go
create view SCORE_View_CDEPT
as select sc.sno,sname,cname,score from sc
join student on student.sno=sc.sno and dept='计算机'
join course on course.cno=sc.cno

select * from SCORE_View_CDEPT
--9.给视图S_View 增加一个年龄age字段
go
alter view S_View
as select a.sno,sname,score,b.sex,age from sc a
join student b on b.sno=a.sno

select * from S_View
--10.创建一个教师工资表视图SAL_View，包含字段：教师名tname、性别sex、职	称	prof、工资总和SALSUM、系列dept
go
create view SAL_View
as select tname,sex,prof,sum(sal+comm) as sum,dept from teacher
group by tname,sex,prof,dept

select * from SAL_View

--11.通过视图SAL_View 查询教师“张朋”的工资收入
select sum from SAL_View where tname='张朋'